package crm.control;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import crm.util.DateTime;
import crm.util.Pagination;
import java.sql.ResultSet;



import crm.dbcoon.DBConnect;
import crm.model.Bargain;

public class BargainControl {

	  //静态分页类成员变量
	public static Pagination pageInstance = null;
	
	static void setByRs(Bargain bar,ResultSet rs){
		try{
			bar.setBargain_id(rs.getString("bargain_id"));
			bar.setId(rs.getInt("id"));
			bar.setOffer_id(rs.getInt("offer_id"));
			bar.setDeal_money(rs.getString("deal_money"));
			bar.setReturned_money(rs.getString("returned_money"));
			bar.setLatest_date(rs.getString("latest_date"));
			bar.setIs_overdue(rs.getString("is_overdue"));
			bar.setOverdue_reason(rs.getString("overdue_reason"));
			bar.setPdf(rs.getString("pdf"));
			bar.setTitle(rs.getString("title"));
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * 根据id获得一条合同记录
	 * @param id
	 * @return
	 */
	public static Bargain getBargainById(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from control_bargain where id = "+id;
		Bargain bar = new Bargain();
		ResultSet rs = null;
		dbc.prepareStatement(sql);
		try{
			rs = (ResultSet)dbc.executeQuery();
			if(rs.next()){
				setByRs(bar,rs);
			}
			else {
				return null;
			}
		} catch(Exception e) {
			bar = null;
		} finally {
			dbc.close(rs);
			dbc.close();
		}
		return bar;
	}
	
	/**
	 * 添加一条记录
	 * @param bar
	 * @return
	 */
	public static boolean addBargain(Bargain bar){
		DBConnect dbc = new DBConnect();
		String sql = "insert into control_bargain(bargain_id,offer_id,deal_money,returned_money,latest_date,is_overdue,"+
		"overdue_reason,pdf,title) value(?,?,?,?,?,?,?,?,?)";
		int recepitId = OfferControl.getOfferById(bar.getOffer_id()).getRecepit_id();
		int recepitStage = RecepitControl.getRecepitById(recepitId).getStage();
		recepitStage +=1;
		dbc.prepareStatement(sql);
		try{
			int index = 1;
			dbc.setString(index++, bar.getBargain_id());
			dbc.setInt(index++, bar.getOffer_id());
			dbc.setString(index++,bar.getDeal_money());
			dbc.setString(index++, bar.getReturned_money());
			dbc.setString(index++, bar.getLatest_date());
			dbc.setString(index++, bar.getIs_overdue());
			dbc.setString(index++, bar.getOverdue_reason());
			dbc.setString(index++, bar.getPdf());
			dbc.setString(index++, bar.getTitle());
			if(dbc.executeUpdate()&&RecepitControl.updateStage(recepitId, recepitStage)&&RecepitControl.updateReturnedMoney(recepitId, bar.getReturned_money())){
			//在转入合同时，需要执行：合同表插入数据,更改跟单状态，更改当前跟单人回款金额。
				return true;
			}
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("添加bargain出错");
			return false;
		} finally{
			dbc.close();
		}
		return false;
	}
	
	/**
	 * 处理添加回款金额
	 * 同时更新到当前跟单人的汇款金额里面
	 */
	public static boolean addReturnedMoney(int bargainId,String add_money) {
		Bargain bargain = BargainControl.getBargainById(bargainId);
		int recepitId = OfferControl.getOfferById(bargain.getOffer_id()).getRecepit_id();
		float add = Float.parseFloat(add_money);
		float old = Float.parseFloat(bargain.getReturned_money());
		float now = old+add;
		String nowReturnen = String.valueOf(now);
		DBConnect dbc = new DBConnect();
		String sql = "update control_bargain set returned_money = '"+nowReturnen+"' where id= '"+bargainId+"'";
		dbc.prepareStatement(sql);
		try {
			if(RecepitControl.updateReturnedMoney(recepitId, add_money)&&dbc.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			dbc.close();
		}
		return false;
	}
	
	/**
	 * 根据跟单id获取其对应的合同id
	 */
	public static int getBargainIdByRecepitId(int recepitId) {
		DBConnect dbConnect = new DBConnect();
		String sql = "select control_bargain.id from control_bargain,control_offer where control_offer.recepit_id='"+recepitId+"' and control_bargain.offer_id = control_offer.id ";
	    dbConnect.prepareStatement(sql);
	    try {
			ResultSet rSet = dbConnect.executeQuery();
			if(rSet.next()){
			return rSet.getInt(1);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			dbConnect.close();
		}
		return 0;
	}
	/**
	 * 判断当前合同回款日期是否超期
	 * @param bargainId
	 */
	public static void updateIsOverDue(int bargainId) {
		Bargain bargain = getBargainById(bargainId);
		String lastDate = bargain.getLatest_date();
		int l = Integer.parseInt(lastDate.replace("-", "").trim());
		int n = Integer.parseInt(DateTime.Date().replace("-","").trim());
		if(l<n){
			DBConnect dbConnect = new DBConnect();
			String sql = "update control_bargain set is_overdue = '1' where id ='"+bargainId+"'";
			dbConnect.prepareStatement(sql);
			try {
				dbConnect.executeUpdate();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
				System.out.println("更新is_overdue失败");
			}finally{
				dbConnect.close();
			}
		}
	}
	
	/**
	 * 判断一次跟单是否完成（合同金额与回款金额是否一致）
	 * @param bargainId
	 * @return
	 */
	public static boolean isComplete(int bargainId) {
		Bargain bargain = BargainControl.getBargainById(bargainId);
		if(bargain.getDeal_money().equals(bargain.getReturned_money())){
			return true;
		}
		else{
			return false;
		}
	}
	/**
	 *更新是否超期字段
	 * @param bargainId
	 * @param isOverDue
	 */
	
	public static void updateIsOverDue(int bargainId,int isOverDue) {
		DBConnect dbConnect = new DBConnect();
		String sql = "update control_bargain set is_overdue = '"+isOverDue+"' where id='"+bargainId+"'";
		dbConnect.prepareStatement(sql);
		try {
			dbConnect.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("更改是否超期出错");
		}finally{
			dbConnect.close();
		}
	}
	/**
	 * 
	 */
	/**
	 * 删除一条记录
	 * @param id
	 * @return
	 */
	/*public static boolean deleteBargainById(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from control_bargain where id ="+id;
		dbc.prepareStatement(sql);
		try{
			dbc.executeUpdate();
			return true;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("删除bargain出错");
			return false;
		} finally{
			dbc.close();
		}
	}*/
	
	/**
	 * 更新记录
	 * @param bar
	 * @param id
	 * @return
	 */
	/*public static boolean updateBargain(Bargain bar){
		DBConnect dbc = new DBConnect();
		String sql = "update control_bargain set offer_id=?,deal_money=?,returned_money=?,latest_date=?,"+
			"is_overdue=?,overdue_reason=?,pdf=? where id = "+bar.getId();
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, bar.getOffer_id());
			dbc.setString(2,bar.getDeal_money());
			dbc.setString(3, bar.getReturned_money());
			dbc.setString(4, bar.getLatest_date());
			dbc.setString(5, bar.getIs_overdue());
			dbc.setString(6, bar.getOverdue_reason());
			dbc.setString(7, bar.getPdf());
			dbc.executeUpdate();
			return true;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("更新bagain出错！");
			return false;
		} finally{
			dbc.close();
		}
	}*/
	
	/**
	 * 分页显示
	 */
	/*public Collection getBargains(int pages, int rowPerPage, String is_overdue)
	        throws SQLException, Exception {
	    String sql = "SELECT * FROM thought order by " + is_overdue;
	    pageInstance = new BargainControlPage(sql, rowPerPage);
	    return pageInstance.getPage(pages);
	}*/
	
	
	@SuppressWarnings("unchecked")
	public static Collection searchBargain(String bargainId,String title,String is_overdue,String currebtPerson,int rowsPerPage,int pages){
		if(bargainId==null||bargainId.equals("")){
			bargainId="%";
		}
		if(title==null||title.equals("")){
			title="%";
		}		
		if(is_overdue==null||is_overdue.equals("")){
			is_overdue="%";
		}
		if(currebtPerson==null||currebtPerson.equals("")){
			currebtPerson="%";
		}
		String sqlString = "select control_bargain.* from control_bargain,control_offer,control_recepit where control_bargain.offer_id = control_offer.id " +
				"and control_offer.recepit_id = control_recepit.id and control_recepit.current_person like'%"+currebtPerson+"%' and control_bargain.is_overdue like'"
				+is_overdue+"' and control_bargain.title like '"+title+"' and control_bargain.bargain_id like '"+bargainId+"'";
	    pageInstance = new BargainControlPage(sqlString, rowsPerPage);
	    try{
	    	System.out.println(sqlString);
	    	return pageInstance.getPage(pages);
	    }catch (Exception e) {
	    	e.printStackTrace();
	    	return null;
		}
	}
	public static Boolean bargainIdIsExist(String bargainId) {
		DBConnect dbc = new DBConnect();
		String sql = "select * from control_bargain where bargain_id = '"+bargainId+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs = dbc.executeQuery();
			while (rs.next()) {
				System.out.println("不可以");
				return false;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		System.out.println("可以");
		return true;
	}

	/*public static void main(String args[]) {
		Bargain bar =new Bargain();
		bar.setId(1);
		bar.setOffer_id(1);
		bar.setDeal_money("1000");
		bar.setLatest_date("明天");
		bar.setIs_overdue("1");
		if(addBargain(bar)){
			System.out.println("很好");
		}else{
			System.out.println("不得行");
		}
	}*/
		}
class BargainControlPage extends Pagination{
	public BargainControlPage(String sql,int rowPerPage){
		super(sql,rowPerPage,1);
	}

public Collection<Bargain> packResultSet(ResultSet rs)
          throws SQLException {
		Collection<Bargain> bargains = new ArrayList<Bargain>();
		try{
			while(rs.next()){
				Bargain bar =new Bargain();
				BargainControl.setByRs(bar, rs);
				bargains.add(bar);
				bar = null;
			}
		}catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("Collection<Bargains>出错");
		}
		return bargains;
	}

	}
